from pymongo import MongoClient
import pandas as pd
from pandas import Series, DataFrame


client = MongoClient('127.0.0.1')
db = client['meicai']
col = db['price']

# 读取mongodb
data = DataFrame(list(col.find()))
# 删除m_id多余列
del data['_id']

# 价格，日期字段转成数值型,将无效值强制转换为NaN(coerce),
# data['price'] = pd.to_numeric(data['price']'date']], errors='coerce')
data[['price', 'date']] = data[['price', 'date']].apply(pd.to_numeric)
# 去重复数据,inplace=True表示更新给自己

data.drop_duplicates(('date', 'name'), keep='last', inplace=True)
totoalPrice = data.groupby('date').price.sum()
totalQuality = data.groupby('date').date.count()
#print([i for i in price][0])
print(totoalPrice)
print(totalQuality)


# 根据name字段获取商品名
sheets = data['name'].drop_duplicates()

# 每个商品名对应一个sheet写入excel表格中


def everySheet():
    writer = pd.ExcelWriter('./meicai.xlsx')
    for i in sheets:
        # 获取每个原材料对应的所有数据s
        item = data[data['name'] == i]
        # 按照日期排序
        item = item.sort_values('date')
        try:
            item['mean'] = item['price']/item['quality']
            print(item['mean'])
            item = item[['date', 'meicainame', 'price', 'quality', 'mean']]
            #item = item[['date', 'meicainame', 'price', 'quality']]
        except:
            pass
        # print(item)
        item.to_excel(writer, sheet_name=i, index=False)
    writer.save()


if __name__ == "__main__":
    # everySheet()
    pass
